const mysql = require('mysql2/promise');
const config = require('../config');

// 创建数据库连接池
const pool = mysql.createPool({
  ...config,
  waitForConnections: true,
  connectionLimit: 10,
  queueLimit: 0
});

// 测试数据库连接
async function testConnection() {
  try {
    const connection = await pool.getConnection();
    console.log('✅ 数据库连接成功');
    connection.release();
    return true;
  } catch (error) {
    console.error('❌ 数据库连接失败:', error.message);
    return false;
  }
}

// 执行数据库迁移
async function runMigration() {
  try {
    const connection = await pool.getConnection();
    
    // 确保使用正确的数据库
    await connection.query(`USE match_data`);
    
    // 检查venue字段是否存在
    const [columns] = await connection.execute(`
      SELECT COLUMN_NAME 
      FROM INFORMATION_SCHEMA.COLUMNS 
      WHERE TABLE_SCHEMA = 'match_data' 
      AND TABLE_NAME = 'matches' 
      AND COLUMN_NAME = 'venue'
    `);
    
    if (columns.length === 0) {
      console.log('🔄 开始执行数据库迁移...');
      
      // 删除旧的不需要的字段
      try {
        // 检查并删除 player_a 字段
        const [playerAColumns] = await connection.execute(`
          SELECT COLUMN_NAME 
          FROM INFORMATION_SCHEMA.COLUMNS 
          WHERE TABLE_SCHEMA = 'match_data' 
          AND TABLE_NAME = 'matches' 
          AND COLUMN_NAME = 'player_a'
        `);
        if (playerAColumns.length > 0) {
          await connection.execute('ALTER TABLE matches DROP COLUMN player_a');
          console.log('✅ 删除 player_a 字段');
        }
        
        // 检查并删除 player_b 字段
        const [playerBColumns] = await connection.execute(`
          SELECT COLUMN_NAME 
          FROM INFORMATION_SCHEMA.COLUMNS 
          WHERE TABLE_SCHEMA = 'match_data' 
          AND TABLE_NAME = 'matches' 
          AND COLUMN_NAME = 'player_b'
        `);
        if (playerBColumns.length > 0) {
          await connection.execute('ALTER TABLE matches DROP COLUMN player_b');
          console.log('✅ 删除 player_b 字段');
        }
        
        // 检查并删除 big_score 字段
        const [bigScoreColumns] = await connection.execute(`
          SELECT COLUMN_NAME 
          FROM INFORMATION_SCHEMA.COLUMNS 
          WHERE TABLE_SCHEMA = 'match_data' 
          AND TABLE_NAME = 'matches' 
          AND COLUMN_NAME = 'big_score'
        `);
        if (bigScoreColumns.length > 0) {
          await connection.execute('ALTER TABLE matches DROP COLUMN big_score');
          console.log('✅ 删除 big_score 字段');
        }
        
        // 检查并删除 detailed_score 字段
        const [detailedScoreColumns] = await connection.execute(`
          SELECT COLUMN_NAME 
          FROM INFORMATION_SCHEMA.COLUMNS 
          WHERE TABLE_SCHEMA = 'match_data' 
          AND TABLE_NAME = 'matches' 
          AND COLUMN_NAME = 'detailed_score'
        `);
        if (detailedScoreColumns.length > 0) {
          await connection.execute('ALTER TABLE matches DROP COLUMN detailed_score');
          console.log('✅ 删除 detailed_score 字段');
        }
        
        console.log('✅ 删除旧字段完成');
      } catch (error) {
        console.log('ℹ️ 删除旧字段时出错:', error.message);
      }
      
      // 添加venue字段
      await connection.execute(`
        ALTER TABLE matches 
        ADD COLUMN venue VARCHAR(255) NOT NULL DEFAULT '未知地点' COMMENT '赛事地点'
      `);
      
      // 更新tournament_type的默认值
      await connection.execute(`
        ALTER TABLE matches 
        MODIFY COLUMN tournament_type VARCHAR(100) DEFAULT 'WTT常规赛' COMMENT '赛事类型'
      `);
      
      // 清空现有数据并重新插入
      await connection.execute('DELETE FROM matches');
      
      // 插入新的示例数据
      const insertSQL = `
        INSERT INTO matches (tournament_name, tournament_type, tournament_score, tournament_intro, venue, match_time, status, remarks) VALUES
        ('2024春季赛', 'WTT常规赛', 100, '2024年春季乒乓球锦标赛', '北京体育馆', '2024-03-15 14:30:00', 'completed', '精彩对决'),
        ('2024春季赛', 'WTT常规赛', 100, '2024年春季乒乓球锦标赛', '上海体育馆', '2024-03-15 16:00:00', 'completed', '激烈比赛'),
        ('2024夏季赛', 'WTT大满贯', 200, '2024年夏季乒乓球锦标赛', '广州体育馆', '2024-06-20 10:00:00', 'completed', '实力悬殊'),
        ('2024夏季赛', 'WTT大满贯', 200, '2024年夏季乒乓球锦标赛', '深圳体育馆', '2024-06-20 15:30:00', 'completed', '逆转取胜'),
        ('2024秋季赛', '友谊赛', 50, '2024年秋季乒乓球友谊赛', '成都体育馆', '2024-09-10 13:00:00', 'ongoing', '进行中')
      `;
      await connection.execute(insertSQL);
      console.log('✅ 数据库迁移完成');
    } else {
      console.log('ℹ️ 数据库结构已是最新，跳过迁移');
    }
    
    connection.release();
    return true;
  } catch (error) {
    console.error('❌ 数据库迁移失败:', error.message);
    return false;
  }
}

// 初始化数据库表
async function initDatabase() {
  try {
    const connection = await pool.getConnection();
    
    // 创建数据库
    await connection.query(`CREATE DATABASE IF NOT EXISTS match_data CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci`);
    await connection.query(`USE match_data`);
    
    // 创建表
    const createTableSQL = `
      CREATE TABLE IF NOT EXISTS matches (
        id INT AUTO_INCREMENT PRIMARY KEY,
        tournament_name VARCHAR(255) NOT NULL COMMENT '赛事名称',
        tournament_type VARCHAR(100) DEFAULT 'WTT常规赛' COMMENT '赛事类型',
        tournament_score INT DEFAULT 0 COMMENT '赛事积分',
        tournament_intro TEXT COMMENT '赛事简介',
        venue VARCHAR(255) NOT NULL COMMENT '赛事地点',
        match_time DATETIME NOT NULL COMMENT '比赛时间',
        status ENUM('pending', 'ongoing', 'completed', 'cancelled') DEFAULT 'pending' COMMENT '比赛状态',
        remarks TEXT COMMENT '备注',
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
      ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
    `;
    
    await connection.execute(createTableSQL);
    
    // 执行迁移
    await runMigration();
    
    connection.release();
    console.log('✅ 数据库初始化完成');
    return true;
  } catch (error) {
    console.error('❌ 数据库初始化失败:', error.message);
    return false;
  }
}

module.exports = {
  pool,
  testConnection,
  initDatabase
};
